package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import utils.DBconn;


//用来对维修的数据库表进行增删改查
public class FixDao {
	 public void addFixStu(String Fix_id,String id,String Fix_Buliding,String Fix_floor,String Fix_BuilNum,String Fix_Time,String Fix_detail) throws ClassNotFoundException {
	    	String Fix_type="0";//宿舍维修设为0
	        String sql = "INSERT INTO fix(Fix_id,id, Fix_Buliding,Fix_floor,Fix_BuilNum,Fix_Time,Fix_detail,Fix_type,Fixed) VALUES(?,?, ?, ?,?,?,?,?,0)";
	        try  {
	        	Connection conn = DBconn.getConnection();
	             PreparedStatement pstmt = conn.prepareStatement(sql);
	            //需要实现参数化
	             pstmt.setString(1, Fix_id);
	             pstmt.setString(2, id);
	             pstmt.setString(3, Fix_Buliding);
	             pstmt.setString(4, Fix_floor);
	             pstmt.setString(5, Fix_BuilNum);
	             pstmt.setString(6, Fix_Time);
	             pstmt.setString(7, Fix_detail);
	             pstmt.setString(8, Fix_type);
	            pstmt.executeUpdate();

	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    public void addFixSg(String Fix_id,String id,String Fix_Buliding,String Fix_floor,String Fix_Time,String Fix_detail) throws ClassNotFoundException {
	    	String Fix_type="1";//公物维修设为1
	    	String Fix_BuilNum="null";
	        String sql = "INSERT INTO fix(Fix_id,id ,Fix_Buliding,Fix_floor,Fix_BuilNum,Fix_Time,Fix_detail,Fix_type,Fixed) VALUES(?,? ,?, ?,?,?,?,?,0)";//未解决为0
	        try  {
	        	Connection conn = DBconn.getConnection();
	             PreparedStatement pstmt = conn.prepareStatement(sql);
	            //需要实现参数化
	             pstmt.setString(1, Fix_id);
	             pstmt.setString(2, id);
	             pstmt.setString(3, Fix_Buliding);
	             pstmt.setString(4, Fix_floor);
	             pstmt.setString(5, Fix_BuilNum);
	             pstmt.setString(6, Fix_Time);
	             pstmt.setString(7, Fix_detail);
	             pstmt.setString(8, Fix_type);
	            pstmt.executeUpdate();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	    //可以在jsp页面列出所有未报修的表单
	    public List<Map<String, Object>> getAllFixes() throws ClassNotFoundException, SQLException {
	        List<Map<String, Object>> fixesList = new ArrayList<>();
	        String sql = "SELECT * FROM fix";
	 
	        try (Connection conn = DBconn.getConnection();
	             PreparedStatement pstmt = conn.prepareStatement(sql);
	             ResultSet rs = pstmt.executeQuery()) {
	 
	            while (rs.next()) {
	                Map<String, Object> fix = new HashMap<>();
	                fix.put("Fix_id", rs.getString("Fix_id"));
	                fix.put("Fix_Buliding", rs.getString("Fix_Buliding"));
	                fix.put("Fix_floor", rs.getString("Fix_floor"));
	                fix.put("Fix_BuilNum", rs.getString("Fix_BuilNum"));
	                fix.put("Fix_Time", rs.getString("Fix_Time"));
	                fix.put("Fix_detail", rs.getString("Fix_detail"));
	                fix.put("Fix_Picture", rs.getString("Fix_Picture"));
	                fix.put("Fix_type", rs.getString("Fix_type"));
	                fixesList.add(fix);
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	            throw e;
	        }
	 
	        return fixesList;
	    }
	    
	    public int getTotalFixCount() throws ClassNotFoundException, SQLException {
	        String sql = "SELECT COUNT(*) FROM fix";
	        int count = 0;

	        try {
	            Connection conn = DBconn.getConnection();
	            Statement stmt = conn.createStatement();
	            ResultSet rs = stmt.executeQuery(sql);
	            if (rs.next()) {
	                count = rs.getInt(1);
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }

	        return count;
	    }
	    
	    //用来产看报修记录
	    public List<Map<String, Object>> getFixesById(String id) throws ClassNotFoundException, SQLException {
	        List<Map<String, Object>> fixesList = new ArrayList<>();
	        String sql = "SELECT * FROM fix WHERE id = ?";

	        try (
	        		Connection conn = DBconn.getConnection();
	             PreparedStatement pstmt = conn.prepareStatement(sql)) {

	            pstmt.setString(1, id);
	            ResultSet rs = pstmt.executeQuery();

	            while (rs.next()) {
	                Map<String, Object> fix = new HashMap<>();
	                fix.put("Fix_id", rs.getString("Fix_id"));
	                fix.put("Fix_Buliding", rs.getString("Fix_Buliding"));
	                fix.put("Fix_floor", rs.getString("Fix_floor"));
	                fix.put("Fix_BuilNum", rs.getString("Fix_BuilNum"));
	                fix.put("Fix_Time", rs.getString("Fix_Time"));
	                fix.put("Fix_detail", rs.getString("Fix_detail"));
	                fix.put("Fix_type", rs.getString("Fix_type"));
	                fixesList.add(fix);
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	            throw e;
	        }

	        return fixesList;
	    }
}
